import pymysql
# 断言方法
import json
import os
from bs4 import BeautifulSoup
from config import DIR_PATH


def common_assert(self, result, expect_code=None, expect_text=None, expect_json=None):
    try:
        if expect_code:  # expect_code不等于None条件成立
            self.assertEqual(expect_code, result.status_code)
        if expect_text:
            self.assertIn(expect_text, result.text)
        if expect_json:
            self.assertIn(expect_json, result.json())
    except Exception as e:
        raise e


# 读取json
def read_json(filename, key):
    file = DIR_PATH + os.sep + "data" + os.sep + filename
    arr = []
    with open(file, "r", encoding="utf-8")as f:
        for data in json.load(f).get(key):
            arr.append(tuple(data.values())[1:])
        return arr


# 提取html工具
def parser_html(result):
    # 1、提取html
    html = result.json().get("description").get("form")
    # 获取soup对象
    soup = BeautifulSoup(html, "html.parser")
    # 2、提取url
    url = soup.form.get("action")
    data = {}
    # 3、遍历查找所有input
    for input in soup.find_all("input"):
        data[input.get("name")] = input.get("value")
    # 4、返回结果 ("http://www.xxxx.xxxx",{"version":10,"xxxx})
    return url, data


# 连接mysql
def exe_sql(sql):
    conn = None
    cursor = None
    try:
        # 获取连接对象
        conn = pymysql.connect(host="121.43.169.97", user="root", password="Itcast_p2p_20191228",
                               database="czbk_member", port=3306,
                               charset="utf8", autocommit=True)
        # 获取游标对象
        cursor = conn.cursor()
        # 执行SQL方法
        cursor.execute(sql)
        if sql.lower().split(" ")[0] == "select":
            # 返回所有结果 -- 查询使用
            return cursor.fetchall()
        else:
            # 返回受影响的行数 -- 非查询使用
            return cursor.rowcount
    except Exception as e:
        # 日志
        print("错误原因：", e)
        # 回滚
        conn.rollback()
        # 抛异常
        raise
    finally:
        # 关闭游标对象
        conn.close()
        # 关闭连接对象
        cursor.close()


# 清除测试数据
def clear_data():
    sql1 = """
        DELETE i.* from mb_member_info as i INNER JOIN mb_member as m on m.id = i.member_id where  m.phone in ( "13600001111", "13600001112","13600001113","13600001114","13600001115");
    """
    exe_sql(sql1)
    sql2 = """
        DELETE l.* from mb_member_login_log as l INNER JOIN mb_member as m on m.id = l.member_id where  m.phone in ( "13600001111", "13600001112","13600001113","13600001114","13600001115");
    """
    exe_sql(sql2)
    sql3 = """
        DELETE from mb_member where phone in( "13600001111", "13600001112","13600001113","13600001114","13600001115");
    """
    exe_sql(sql3)
    sql4 = """
        DELETE from mb_member_register_log where phone in ( "13600001111", "13600001112","13600001113","13600001114","13600001115");
    """
    exe_sql(sql4)


# 构造借款业务数据
def build_loan_data():
    sql = """
    -- 清空操作
        delete from `czbk_member`.`mb_member` where id=869;
        delete from `czbk_member`.`mb_member_info` where id=987;
        delete from `czbk_finance`.`fn_loan_amount` where id=987;
        delete from `p2p_mock`.`p2p_account` where id=911;
        -- 借款
        delete from `czbk_finance`.`fn_loan` where id=642;
        delete from `czbk_finance`.`fn_loan_info` where id=832;
        delete from `czbk_finance`.`fn_loan_amount_log` where id=869;

        INSERT INTO `czbk_member`.`mb_member` (`id`, `name`, `trust_account`, `password`, `email`, `phone`, `paypassword`, `count`, `register_ip`, `register_time`, `register_date`, `lastlogin_ip`, `lastlogin_time`, `lastlogin_date`, `register_type`, `role`, `group`, `group_status`, `is_realname`, `is_email`, `is_phone`, `is_video`, `is_id5`, `is_vip`, `credit_point`, `is_auto`, `pwd_attach`, `status`, `lock_time`, `vip_start_time`, `vip_end_time`, `vip_category_id`, `vip_category_ind`, `amount`, `phonestr`) VALUES ('869', '13099775533', '6021234507160403', 'eab70f4a46c640b3dfc35040bf37c803', NULL, '13099775533', NULL, '1', '1972826569', '1588835827', '2020-05-07', '1972826569', '1588835827', '2020-05-07', '1', '1', '1', '2', '1', '-1', '1', '-1', '-1', '-1', '0', '-1', '1m4U82PqPx', '1', NULL, NULL, NULL, NULL, NULL, '0.00', NULL);
        INSERT INTO `czbk_member`.`mb_member_info` (`id`, `member_id`, `member_name`, `avatar`, `nickname`, `realname`, `gender`, `birthday`, `card_id`, `qq`, `educational_background`, `marry_status`, `graduated`, `company_industry`, `company_scale`, `company_office`, `monthly_income`, `invite_money`, `hometown_province`, `hometown_city`, `hometown_area`, `hometown_post`) VALUES ('987', '869', '13099775533', NULL, NULL, '小石头', NULL, '1986-11-18', '429009118611181111', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
        INSERT INTO `czbk_finance`.`fn_loan_amount` (`id`, `member_id`, `member_name`, `credit_amount`, `credit_amount_freeze`, `vouch_amount`, `vouch_amount_freeze`, `pawn_amount`, `pawn_amount_freeze`, `roam_amount`, `roam_amount_freeze`) VALUES ('987', '869', '13099775533', '200000.000000', '0.000000', '0.000000', '0.000000', '0.000000', '0.000000', '0.000000', '0.000000');
        INSERT INTO `p2p_mock`.`p2p_account` (`id`, `account`, `AvlBal`, `AcctBal`, `FrzBal`, `create_time`) VALUES ('911', '6021234507160403', '0.00', '0.00', '0.00', '2020-05-07 16:04:03');

        -- 借款
        INSERT INTO `czbk_finance`.`fn_loan` (`id`, `ind`, `serialno`, `name`, `member_id`, `member_name`, `amount`, `credited_amount`, `progress`, `tender_count`, `category_id`, `category_type`, `repay_type`, `period`, `apr`, `sort_index`, `sort_top`, `status`, `hidden_status`, `additional_status`, `deposit_certificate`, `certificate_file_id`, `loan_repay_status`, `loan_repay_time`, `overdue_time`, `add_date`, `add_time`, `verify_time`, `reverify_time`, `add_ip`, `vouch_company_id`, `op_status`, `marker_type`) VALUES ('642', 'f5380d59791773a8abad4141ff90a5a4', '202008231111', '数据库构造借款1', '869', '13099775533', '100000.00', '0.00', '0.00', '0', '1', '1', '1', '60', '7.55', '1', NULL, '3', '1', '-1', '-1', NULL, NULL, NULL, '1609501013', '2020-05-07', '1588847800', '1588851413', NULL, '1972826569', '0', '-1', '数据库');
        INSERT INTO `czbk_finance`.`fn_loan_info` (`id`, `loan_id`, `thumbs`, `contents`, `attachment_ids`, `password`, `use`, `tender_amount_min`, `tender_amount_max`, `freeze_amount`, `freeze_amount_proportion`, `freeze_period`, `award_status`, `fail_award_status`, `award_amount`, `award_proportion`, `award_amount_total`, `validate`, `part_status`, `tender_count`, `comment_status`, `comment_count`, `is_company`, `company_name`, `vouch_company_info`, `vouch_company_pic`, `vouch_company_guaranty`, `amount_category_id`, `hits`, `cancel_admin_id`, `cancel_remark`, `cancel_time`, `verify_admin_id`, `verify_admin_name`, `verify_remark`, `verify_time`, `verify_ip`, `reverify_admin_id`, `reverify_admin_name`, `reverify_remark`, `reverify_time`, `reverify_ip`, `auto_scale`, `is_auto`, `additional_status`, `additional_apr`, `additional_name`, `additional_amount_max`, `additional_pic`, `product_process`, `information`) VALUES ('832', '642', NULL, '数据库构造借款1', 'a:0:{}', NULL, '10102', '100.00', '1000.00', NULL, '10.00', NULL, '-1', '-1', NULL, NULL, NULL, '239', NULL, '0', '-1', '0', '-1', NULL, NULL, NULL, NULL, '1', '1', NULL, NULL, NULL, '4', 'admin', '数据库构造借款1 通过', '1588851413', '1972826569', NULL, NULL, NULL, NULL, NULL, NULL, '1', '-1', '0.000000', NULL, '0.000000', NULL, NULL, NULL);
        UPDATE `czbk_finance`.`fn_loan_amount` SET `credit_amount_freeze`='100000.000000' WHERE `member_id`='869';
        INSERT INTO `czbk_finance`.`fn_loan_amount_log` (`member_id`, `member_name`, `category_id`, `type`, `amount`, `remark`, 
        `add_time`) VALUES ('869','13099775533', 1, 4, '100000', '借款冻结', '1972826569');	
    """
    exe_sql(sql)
